# Oracle连接问题快速修复脚本
# 针对ORA-12170连接超时问题的立即解决方案

param(
    [Parameter(Mandatory=$false)]
    [string]$OracleHost = "10.3.255.250",
    
    [Parameter(Mandatory=$false)]
    [string]$OraclePort = "15521",
    
    [Parameter(Mandatory=$false)]
    [string]$OracleService = "HISDB",
    
    [Parameter(Mandatory=$false)]
    [string]$UserId = "his_user",
    
    [Parameter(Mandatory=$false)]
    [string]$Password = "his_password"
)

Write-Host "=== Oracle连接问题快速修复 ===" -ForegroundColor Green
Write-Host "目标服务器: $OracleHost:$OraclePort/$OracleService" -ForegroundColor Yellow
Write-Host ""

# 1. 网络连通性测试
function Test-NetworkConnectivity {
    Write-Host "🌐 测试网络连通性..." -ForegroundColor Blue
    
    # Ping测试
    Write-Host "  📡 Ping测试..." -ForegroundColor Yellow
    $pingResult = Test-Connection -ComputerName $OracleHost -Count 3 -Quiet
    if ($pingResult) {
        Write-Host "    ✓ Ping测试成功" -ForegroundColor Green
    } else {
        Write-Host "    ❌ Ping测试失败" -ForegroundColor Red
        return $false
    }
    
    # 端口连通性测试
    Write-Host "  🔌 端口连通性测试..." -ForegroundColor Yellow
    try {
        $tcpClient = New-Object System.Net.Sockets.TcpClient
        $tcpClient.ReceiveTimeout = 5000
        $tcpClient.SendTimeout = 5000
        $tcpClient.Connect($OracleHost, $OraclePort)
        $tcpClient.Close()
        Write-Host "    ✓ 端口 $OraclePort 连通" -ForegroundColor Green
        return $true
    } catch {
        Write-Host "    ❌ 端口 $OraclePort 不通: $($_.Exception.Message)" -ForegroundColor Red
        return $false
    }
}

# 2. 生成优化的连接字符串
function Generate-OptimizedConnectionString {
    Write-Host "🔧 生成优化的连接字符串..." -ForegroundColor Blue
    
    # 基础连接字符串
    $baseConnectionString = "Data Source=$OracleHost`:$OraclePort/$OracleService;User Id=$UserId;Password=$Password;"
    
    # 优化参数
    $optimizations = @(
        "Connection Timeout=90",           # 增加连接超时到90秒
        "Pooling=true",                    # 启用连接池
        "Min Pool Size=1",                 # 最小连接池
        "Max Pool Size=10",                # 最大连接池
        "Connection Lifetime=300",         # 连接生命周期5分钟
        "Incr Pool Size=1",                # 连接池增长步长
        "Decr Pool Size=1",                # 连接池减少步长
        "Validate Connection=true",        # 验证连接
        "Load Balancing=false",            # 关闭负载均衡（单服务器）
        "Self Tuning=true",                # 启用自调优
        "Statement Cache Size=10",         # 语句缓存
        "Persist Security Info=false"      # 不持久化安全信息
    )
    
    $optimizedConnectionString = $baseConnectionString + ($optimizations -join ";")
    
    Write-Host "  📝 优化的连接字符串:" -ForegroundColor Yellow
    Write-Host "    $optimizedConnectionString" -ForegroundColor Gray
    
    return $optimizedConnectionString
}

# 3. 更新配置文件
function Update-ConfigurationFiles {
    param([string]$ConnectionString)
    
    Write-Host "📁 更新配置文件..." -ForegroundColor Blue
    
    # 备份原文件
    $timestamp = Get-Date -Format "yyyyMMdd_HHmmss"
    
    # 更新appsettings.json
    $appsettingsPath = "appsettings.json"
    if (Test-Path $appsettingsPath) {
        Copy-Item $appsettingsPath "$appsettingsPath.backup_$timestamp"
        
        $appsettings = Get-Content $appsettingsPath | ConvertFrom-Json
        $appsettings.ConnectionStrings.HisOracleConnection = $ConnectionString
        
        $appsettings | ConvertTo-Json -Depth 10 | Set-Content $appsettingsPath -Encoding UTF8
        Write-Host "    ✓ 已更新 appsettings.json" -ForegroundColor Green
    }
    
    # 更新DatabaseConfigurationService.cs
    $dbConfigPath = "Services/DatabaseConfigurationService.cs"
    if (Test-Path $dbConfigPath) {
        Copy-Item $dbConfigPath "$dbConfigPath.backup_$timestamp"
        
        $content = Get-Content $dbConfigPath -Raw
        
        # 查找并替换DefaultConnections.HisOracle
        $pattern = '(HisOracle\s*=\s*")([^"]*)("[^;]*;)'
        $replacement = "`${1}$ConnectionString`${3}"
        
        $newContent = $content -replace $pattern, $replacement
        
        $newContent | Set-Content $dbConfigPath -Encoding UTF8
        Write-Host "    ✓ 已更新 DatabaseConfigurationService.cs" -ForegroundColor Green
    }
}

# 4. 创建连接测试工具
function Create-ConnectionTestTool {
    Write-Host "🔍 创建连接测试工具..." -ForegroundColor Blue
    
    $testToolContent = @"
using Oracle.ManagedDataAccess.Client;
using System.Diagnostics;

namespace Lzfy_His_Service.Tools
{
    /// <summary>
    /// Oracle连接快速测试工具
    /// </summary>
    public static class QuickOracleConnectionTest
    {
        /// <summary>
        /// 快速测试Oracle连接
        /// </summary>
        public static async Task<QuickTestResult> TestConnectionAsync(string connectionString)
        {
            var result = new QuickTestResult
            {
                ConnectionString = connectionString,
                StartTime = DateTime.Now
            };
            
            var stopwatch = Stopwatch.StartNew();
            
            try
            {
                Console.WriteLine(`"开始快速Oracle连接测试...`");
                Console.WriteLine(`"连接字符串: {0}`", connectionString);
                
                using var connection = new OracleConnection(connectionString);
                
                // 测试连接打开
                await connection.OpenAsync();
                result.ConnectionTime = stopwatch.Elapsed;
                result.IsConnected = true;
                
                // 测试简单查询
                using var command = connection.CreateCommand();
                command.CommandText = `"SELECT 1 FROM DUAL`";
                var queryResult = await command.ExecuteScalarAsync();
                
                result.QueryTest = queryResult?.ToString() == `"1`";
                result.IsSuccess = true;
                result.Message = `"连接测试成功`";
                
                Console.WriteLine(`"✅ 连接测试成功，耗时: {0}ms`", stopwatch.ElapsedMilliseconds);
            }
            catch (OracleException oex)
            {
                result.IsSuccess = false;
                result.IsConnected = false;
                result.OracleErrorCode = oex.Number;
                result.Message = oex.Message;
                result.Exception = oex;
                
                Console.WriteLine(`"❌ Oracle连接失败: {0} (错误代码: {1})`", oex.Message, oex.Number);
                
                // 提供针对性建议
                ProvideTroubleshootingAdvice(oex.Number);
            }
            catch (Exception ex)
            {
                result.IsSuccess = false;
                result.IsConnected = false;
                result.Message = ex.Message;
                result.Exception = ex;
                
                Console.WriteLine(`"❌ 连接失败: {0}`", ex.Message);
            }
            finally
            {
                stopwatch.Stop();
                result.EndTime = DateTime.Now;
                result.TotalTime = stopwatch.Elapsed;
            }
            
            return result;
        }
        
        private static void ProvideTroubleshootingAdvice(int errorCode)
        {
            switch (errorCode)
            {
                case 12170:
                    Console.WriteLine(`"💡 建议: ORA-12170 连接超时`");
                    Console.WriteLine(`"  - 增加Connection Timeout参数`");
                    Console.WriteLine(`"  - 检查网络延迟和防火墙`");
                    Console.WriteLine(`"  - 验证Oracle监听器状态`");
                    break;
                case 12514:
                    Console.WriteLine(`"💡 建议: ORA-12514 监听器不知道服务`");
                    Console.WriteLine(`"  - 检查服务名称是否正确`");
                    Console.WriteLine(`"  - 验证tnsnames.ora配置`");
                    break;
                case 1017:
                    Console.WriteLine(`"💡 建议: ORA-01017 用户名/密码无效`");
                    Console.WriteLine(`"  - 检查用户名和密码`");
                    Console.WriteLine(`"  - 确认用户是否被锁定`");
                    break;
                default:
                    Console.WriteLine(`"💡 建议: 检查Oracle错误代码 {0} 的官方文档`", errorCode);
                    break;
            }
        }
    }
    
    public class QuickTestResult
    {
        public string ConnectionString { get; set; } = string.Empty;
        public DateTime StartTime { get; set; }
        public DateTime EndTime { get; set; }
        public TimeSpan TotalTime { get; set; }
        public TimeSpan ConnectionTime { get; set; }
        public bool IsSuccess { get; set; }
        public bool IsConnected { get; set; }
        public bool QueryTest { get; set; }
        public string Message { get; set; } = string.Empty;
        public int? OracleErrorCode { get; set; }
        public Exception? Exception { get; set; }
    }
}
"@
    
    New-Item -ItemType Directory -Force -Path "Tools" | Out-Null
    $testToolPath = "Tools/QuickOracleConnectionTest.cs"
    $testToolContent | Set-Content $testToolPath -Encoding UTF8
    Write-Host "    ✓ 已创建快速连接测试工具" -ForegroundColor Green
}

# 5. 创建增强的重试机制
function Create-EnhancedRetryMechanism {
    Write-Host "🔄 创建增强的重试机制..." -ForegroundColor Blue
    
    $retryFactoryContent = @"
using System.Data;
using Oracle.ManagedDataAccess.Client;
using Microsoft.Extensions.Logging;
using Polly;
using Polly.Retry;
using System.Diagnostics;

namespace Lzfy_His_Service.Services
{
    /// <summary>
    /// 快速修复版本的弹性Oracle连接工厂
    /// </summary>
    public class QuickFixOracleConnectionFactory : IOracleConnectionFactory
    {
        private readonly ResiliencePipeline _retryPipeline;
        private readonly string _connectionString;
        private readonly ILogger<QuickFixOracleConnectionFactory> _logger;
        
        public QuickFixOracleConnectionFactory(string connectionString, ILogger<QuickFixOracleConnectionFactory> logger)
        {
            _connectionString = connectionString;
            _logger = logger;
            
            // 更激进的重试策略
            _retryPipeline = new ResiliencePipelineBuilder()
                .AddRetry(new RetryStrategyOptions
                {
                    ShouldHandle = new PredicateBuilder().Handle<OracleException>(IsRetriableError),
                    MaxRetryAttempts = 5,
                    Delay = TimeSpan.FromSeconds(2),
                    BackoffType = DelayBackoffType.Exponential,
                    MaxDelay = TimeSpan.FromSeconds(60),
                    OnRetry = args =>
                    {
                        _logger.LogWarning(`"Oracle连接失败，{Delay}秒后进行第{AttemptNumber}次重试. 错误: {Error}`", 
                            args.RetryDelay.TotalSeconds, args.AttemptNumber, args.Outcome.Exception?.Message);
                        return ValueTask.CompletedTask;
                    }
                })
                .Build();
        }
        
        public IDbConnection CreateConnection()
        {
            var connection = new OracleConnection(_connectionString);
            
            // 设置连接事件处理
            connection.InfoMessage += (sender, args) =>
            {
                _logger.LogInformation(`"Oracle信息: {Message}`", args.Message);
            };
            
            return connection;
        }
        
        public async Task<IDbConnection> CreateAndOpenConnectionAsync()
        {
            return await _retryPipeline.ExecuteAsync(async _ =>
            {
                var stopwatch = Stopwatch.StartNew();
                
                _logger.LogInformation(`"尝试建立Oracle连接...`");
                
                var connection = CreateConnection() as OracleConnection;
                if (connection == null)
                {
                    throw new InvalidOperationException(`"无法创建Oracle连接对象`");
                }
                
                try
                {
                    await connection.OpenAsync();
                    stopwatch.Stop();
                    
                    _logger.LogInformation(`"Oracle连接成功建立，耗时: {ElapsedMs}ms`", stopwatch.ElapsedMilliseconds);
                    
                    // 验证连接
                    using var testCommand = connection.CreateCommand();
                    testCommand.CommandText = `"SELECT 1 FROM DUAL`";
                    await testCommand.ExecuteScalarAsync();
                    
                    _logger.LogInformation(`"Oracle连接验证成功`");
                    
                    return connection;
                }
                catch
                {
                    connection?.Dispose();
                    throw;
                }
            });
        }
        
        private static bool IsRetriableError(OracleException ex)
        {
            // 扩展的可重试错误列表
            var retriableErrors = new[]
            {
                // 网络和连接超时
                12170, 12171, 12224, 12225, 12514, 12518, 12519, 12520, 12521, 12522, 12523, 12524, 12525,
                12526, 12527, 12528, 12529, 12530, 12531, 12532, 12533, 12534, 12535, 12536, 12537, 12538,
                
                // 连接中断
                3113, 3114, 1012, 1033, 1034, 1089, 1090,
                
                // 资源和超时
                60, 4020, 4021, 30006, 24756, 25408, 29276, 24347,
                
                // 临时性错误
                1, 18, 28, 1041, 1043, 1045, 1092
            };
            
            return retriableErrors.Contains(ex.Number);
        }
    }
}
"@
    
    $retryFactoryPath = "Services/QuickFixOracleConnectionFactory.cs"
    $retryFactoryContent | Set-Content $retryFactoryPath -Encoding UTF8
    Write-Host "    ✓ 已创建增强的重试机制" -ForegroundColor Green
}

# 6. 编译和测试
function Build-AndTest {
    Write-Host "🔨 编译和测试..." -ForegroundColor Blue
    
    # 编译项目
    Write-Host "  📦 编译项目..." -ForegroundColor Yellow
    dotnet build --configuration Release
    
    if ($LASTEXITCODE -eq 0) {
        Write-Host "    ✓ 编译成功" -ForegroundColor Green
    } else {
        Write-Host "    ❌ 编译失败，请检查代码" -ForegroundColor Red
        return $false
    }
    
    return $true
}

# 主执行流程
Write-Host "🚀 开始执行Oracle连接快速修复..." -ForegroundColor Green
Write-Host ""

# 1. 网络连通性测试
if (-not (Test-NetworkConnectivity)) {
    Write-Host "❌ 网络连通性测试失败，请检查网络配置" -ForegroundColor Red
    exit 1
}

# 2. 生成优化的连接字符串
$optimizedConnectionString = Generate-OptimizedConnectionString

# 3. 更新配置文件
Update-ConfigurationFiles -ConnectionString $optimizedConnectionString

# 4. 创建测试工具和增强机制
Create-ConnectionTestTool
Create-EnhancedRetryMechanism

# 5. 编译测试
if (Build-AndTest) {
    Write-Host "✅ Oracle连接快速修复完成！" -ForegroundColor Green
    Write-Host ""
    Write-Host "📋 修复内容:" -ForegroundColor Blue
    Write-Host "  ✓ 连接超时增加到90秒" -ForegroundColor Green
    Write-Host "  ✓ 优化连接池参数" -ForegroundColor Green
    Write-Host "  ✓ 启用连接验证" -ForegroundColor Green
    Write-Host "  ✓ 增强重试机制" -ForegroundColor Green
    Write-Host "  ✓ 创建快速测试工具" -ForegroundColor Green
    Write-Host ""
    Write-Host "🔄 下一步操作:" -ForegroundColor Blue
    Write-Host "  1. 重启应用程序" -ForegroundColor Gray
    Write-Host "  2. 测试Oracle连接" -ForegroundColor Gray
    Write-Host "  3. 监控连接日志" -ForegroundColor Gray
    Write-Host ""
    Write-Host "🧪 测试命令:" -ForegroundColor Blue
    Write-Host "  Invoke-RestMethod -Uri 'http://localhost:6731/refundtaskapi/api/diagnostics/test-oracle-connection' -Method POST" -ForegroundColor Gray
} else {
    Write-Host "❌ 快速修复过程中出现错误" -ForegroundColor Red
    exit 1
}

Write-Host ""
Write-Host "📞 如果问题仍然存在，请考虑:" -ForegroundColor Yellow
Write-Host "  - 检查Oracle服务器负载" -ForegroundColor Gray
Write-Host "  - 联系网络管理员检查防火墙" -ForegroundColor Gray
Write-Host "  - 验证Oracle监听器配置" -ForegroundColor Gray
Write-Host "  - 考虑使用其他Oracle连接框架" -ForegroundColor Gray